<?php


class Blackbird_Model_Content extends Zend_Db_Table
{
	function fetchFrontPageArticles($params, $pageIndex)
	{
		$pageSize = $params['mainArticlesCount'] + $params['summaryArticlesCount'] + $params['linkArticlesCount'];
		$offset = $pageIndex * $pageSize;
		$db = $this->getAdapter();
		$sql = 'select a.*, c.name categoryName, c.slug categorySlug, '
			. "\n		mc.name mainCategoryName, mc.slug mainCategorySlug"
			. "\n	from bb_article a"
			. "\n		inner join bb_articlecategory ac on ac.articleId = a.articleId"
			. "\n		inner join bb_category c on c.categoryId = ac.categoryId"
			. "\n		inner join bb_category mc on case when c.parentId is null then mc.categoryId = c.categoryId"
			. "\n			else mc.parentId is null and mc.lft < c.lft and mc.rgt > c.rgt end"
			. "\n	where a.inFrontPage <> 0 and a.isPublished = 1 and a.publishedAt <= CURRENT_TIMESTAMP"
			. "\n		and (a.expiresAt is null or a.expiresAt >= CURRENT_TIMESTAMP)"
			. "\n	order by a.publishedAt desc"
			. "\n	limit $offset, $pageSize"
			;
		$articles = new stdClass();
		$articles->pageIndex = $pageIndex;
		$articles->items = $db->fetchAll($sql);
		$sql = 'select count(*)'
			. "\n	from bb_article a"
			. "\n		inner join bb_articlecategory ac on ac.articleId = a.articleId"
			. "\n		inner join bb_category c on c.categoryId = ac.categoryId"
			. "\n		inner join bb_category mc on case when c.parentId is null then mc.categoryId = c.categoryId"
			. "\n			else mc.parentId is null and mc.lft < c.lft and mc.rgt > c.rgt end"
			. "\n	where a.inFrontPage <> 0 and a.isPublished = 1 and a.publishedAt <= CURRENT_TIMESTAMP"
			. "\n		and (a.expiresAt is null or a.expiresAt >= CURRENT_TIMESTAMP)"
			;
		$articles->pagesCount = ceil($db->fetchOne($sql) / $pageSize);
		foreach ($articles->items as $article)
		{
			$article->url = BASE_URL . '/articles'
				. ($article->mainCategorySlug != $article->categorySlug ? '/' . $article->mainCategorySlug : '')
				. '/' . $article->categorySlug . '/' . $article->slug;
			$article->publishedAt = new Zend_Date($article->publishedAt);
			if (null != $article->expiresAt)
				$article->expiresAt = new Zend_Date($article->expiresAt);
		}
		return $articles;
	}

	function fetchCategoryBySlug($slug)
	{
		$db = $this->getAdapter();
		$sql = 'select * from bb_category'
			. "\n	where slug = " . $db->quote($slug)
			;
		return $db->fetchRow($sql);
	}

	function fetchArticlesByCategoryId($params, $pageIndex, $categoryId)
	{
		$pageSize = $params['mainArticlesCount'] + $params['summaryArticlesCount'] + $params['linkArticlesCount'];
		$categoryId = (int)$categoryId;
		$offset = $pageIndex * $pageSize;
		$sql = 'select a.*, c.name categoryName, c.slug categorySlug, '
			. "\n		mc.name mainCategoryName, mc.slug mainCategorySlug"
			. "\n	from bb_article a"
			. "\n		inner join bb_articlecategory ac on ac.articleId = a.articleId"
			. "\n		inner join bb_category c on c.categoryId = ac.categoryId"
			. "\n		left join bb_category mc on mc.categoryId = c.categoryId"
			. "\n	where c.categoryId = $categoryId and isPublished = 1 and a.publishedAt <= CURRENT_TIMESTAMP"
			. "\n		and (a.expiresAt is null or a.expiresAt >= CURRENT_TIMESTAMP)"
			. "\n	order by a.publishedAt desc"
			. "\n	limit $offset, $pageSize"
			;
		$db = $this->getAdapter();
		$articles = new stdClass();
		$articles->pageIndex = $pageIndex;
		$articles->items = $db->fetchAll($sql);
		$sql = 'select count(*)'
			. "\n	from bb_article a"
			. "\n		inner join bb_articlecategory ac on ac.articleId = a.articleId"
			. "\n		inner join bb_category c on c.categoryId = ac.categoryId"
			. "\n		left join bb_category mc on mc.categoryId = c.categoryId"
			. "\n	where c.categoryId = $categoryId and isPublished = 1 and a.publishedAt <= CURRENT_TIMESTAMP"
			. "\n		and (a.expiresAt is null or a.expiresAt >= CURRENT_TIMESTAMP)"
			;
		$articles->pagesCount = ceil($db->fetchOne($sql) / $pageSize);
		foreach ($articles->items as $article)
		{
			$article->url = BASE_URL . '/articles'
				. ($article->mainCategorySlug != $article->categorySlug ? '/' . $article->mainCategorySlug : '')
				. '/' . $article->categorySlug . '/' . $article->slug;
			$article->publishedAt = new Zend_Date($article->publishedAt);
			if (null != $article->expiresAt)
				$article->expiresAt = new Zend_Date($article->expiresAt);
		}
		return $articles;
	}

	function fetchChildrenByCategoryId($categoryId)
	{
		$sql = 'select slug, name'
			. "\n	from bb_category"
			. "\n	where parentId = " . (int)$categoryId
			. "\n	order by name"
			;
		$db = $this->getAdapter();
		return $db->fetchAll($sql);
	}

	function existsArticleBySlug($slug)
	{
		$db = $this->getAdapter();
		$sql = 'select count(*) from bb_article'
			. "\n	where slug = " . $db->quote($slug) . ' and isPublished = 1 and publishedAt <= CURRENT_TIMESTAMP'
			. "\n		and (expiresAt is null or expiresAt >= CURRENT_TIMESTAMP)"
			;
		return $db->fetchOne($sql) > 0;
	}

	function fetchArticleBySlug($slug)
	{
		$db = $this->getAdapter();
		$sql = 'select a.*, c.name categoryName, c.slug categorySlug, '
			. "\n		mc.name mainCategoryName, mc.slug mainCategorySlug"
			. "\n	from bb_article a"
			. "\n		inner join bb_articlecategory ac on ac.articleId = a.articleId"
			. "\n		inner join bb_category c on c.categoryId = ac.categoryId"
			. "\n		inner join bb_category mc on case when c.parentId is null then mc.categoryId = c.categoryId"
			. "\n			else mc.parentId is null and mc.lft < c.lft and mc.rgt > c.rgt end"
			. "\n	where a.slug = " . $db->quote($slug) . ' and isPublished = 1 and publishedAt <= CURRENT_TIMESTAMP'
			. "\n		and (expiresAt is null or expiresAt >= CURRENT_TIMESTAMP)"
			;
		$article = $db->fetchRow($sql);
		if (false != $article)
		{
			$article->url = BASE_URL . '/articles'
				. ($article->mainCategorySlug != $article->categorySlug ? '/' . $article->mainCategorySlug : '')
				. '/' . $article->categorySlug . '/' . $article->slug;
			$article->publishedAt = new Zend_Date($article->publishedAt);
			if (null != $article->expiresAt)
				$article->expiresAt = new Zend_Date($article->expiresAt);
		}
		return $article;
	}

	function fetchBlocksByPosition($position, $section)
	{
		$db = $this->getAdapter();
		$sql = 'select * from bb_block'
			. "\n	where isPublished <> 0 and position = " . $db->quote($position)
			. "\n		and section = " . $db->quote($section)
			. "\n	order by ordering"
			;
		$blocks = $db->fetchAll($sql);
		foreach ($blocks as $block)
			parse_str($block->params, $block->params);
		return $blocks;
	}

	function fetchCategorySelection()
	{
		$sql = 'select c.slug, c.parentId, c.name'
			. "\n	from bb_category c"
			. "\n	where c.component = 'Articles'"
			. "\n	order by c.lft, c.rgt"
			;
		$db = $this->getAdapter();
		$categories = array();
		foreach ($db->fetchAll($sql) as $row)
			$categories[$row->slug] = $row->name;
		return $categories;
	}

	function fetchArticleSelection()
	{
		$sql = 'select a.slug, a.title'
			. "\n	from bb_article a"
			. "\n	where a.isPublished <> 0"
			. "\n	order by a.title"
			;
		$db = $this->getAdapter();
		$articles = array();
		foreach ($db->fetchAll($sql) as $row)
			$articles[$row->slug] = $row->title;
		return $articles;
	}

	function fetchArticleCommentsFromArticleId($articleId)
	{
		$articleId = (int)$articleId;
		$db = $this->getAdapter();
		$sql = 'select coalesce(u.firstname, c.authorName) authorName, coalesce(u.email, c.authorEmail) authorEmail,'
			. "\n		u.picture userPicture, c.content, c.publishedAt, c.karma"
			. "\n	from bb_articlecomment ac"
			. "\n		inner join bb_comment c on c.commentId = ac.commentId and c.component = 'Articles'"
			. "\n		left join bb_user u on u.userId = c.userId"
			. "\n	where ac.articleId = " . $articleId
			. "\n	order by lft desc, rgt desc"
			. "\n	limit 100"
			;
		return $db->fetchAll($sql);
	}

	function postArticleComment($form)
	{
		$values = $form->getValues();
		$comment = array(
			'lft' => 0,
			'rgt' => 0,
			'content' => $values['content'],
			'publishedAt' => date('Y-m-d H:i:s'),
			'component' => 'Articles',
			'karma' => 0,
			'isApproved' => 1,
		);
		$auth = Zend_Auth::getInstance();
		if ($auth->hasIdentity())
		{
			$identity = $auth->getIdentity();
			$comment['authorName'] = $identity->firstname;
			$comment['authorEmail'] = $identity->email;
		}
		else
		{
			$comment['authorName'] = $values['authorName'];
			$comment['authorEmail'] = $values['authorEmail'];
		}
		$db = $this->getAdapter();
		$db->insert('bb_comment', $comment);
		$comment['commentId'] = $db->lastInsertId();
		$articleComment = array(
			'articleId' => (int)$values['articleId'],
			'commentId' => $comment['commentId'],
		);
		$db->insert('bb_articlecomment', $articleComment);
	}

	function share($form, $article)
	{
		$values = $form->getValues();
		$auth = Zend_Auth::getInstance();
		if ($auth->hasIdentity())
		{
			$user = $auth->getIdentity();
			$values['fromName'] = $user->firstname . ' ' . $user->lastname;
			$values['fromEmail'] = $user->email;
		}

		$view = new Zend_View();
		$view->setScriptPath(LIBRARY_PATH . '/Blackbird/views/scripts/content/email')
			->setEncoding('UTF-8');
		$view->values = $values;
		$view->article = $article;
		$view->render('share.phtml');

		$mail = new Zend_Mail();
		$mail->setBodyText($view->placeholder('bodyText'), 'UTF-8');
		//$mail->setBodyHtml($view->placeholder('bodyHtml'), 'UTF-8');
		$mail->setFrom($view->fromEmail, $view->fromName);
		$mail->addTo($values['toEmail'], $values['toName']);
		$mail->setSubject($view->subject);
		$mail->send();
	}
}

